﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    /// <summary>
    /// 岩心岩屑 2013-5-19修改完成
    /// </summary>
    public class YXInfo
    {
        public YXInfo() { }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回dataSet</returns>
        public DataSet GetAllInfoBySet()
        {
            string strSql = "SELECT * FROM dbo.YX_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        public DataSet GetInfoByID(int ZKID)
        {
            string strSql = "SELECT * FROM dbo.YX_TBL WHERE ID="+ZKID;
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }



        //根据ZKCODE获得记录集
        public DataSet GetInfoByZKCD(string ZKCODE)
        {
            string strSql = "SELECT * FROM dbo.YX_TBL WHERE ZK_CODE='" + ZKCODE+"'";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoByTable()
        {
            string strSql = "SELECT * FROM dbo.YX_INFO_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.YX_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 连接ZL_TBL查询
        /// </summary>
        /// <param name="strExp"></param>
        /// <returns></returns>
        public DataSet GetInfoFromLinkTblByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.YX_INFO_TBL,dbo.ZL_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.YX_INFO_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 分页查询，后台控制页面大小
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strWhere"></param>
        /// <returns>返回IList</returns>
        public IList<YX_TBL> GetInfoByPage(long page, long num, string strWhere)
        {
            string strSql = SQLHelper.GetPagingSQL("dbo.YX_INFO_TBL", "ID", "*", strWhere, "ID DESC", num, page);
            IList<YX_TBL> yxLst = new List<YX_TBL>();

            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    YX_TBL yx_info = new YX_TBL();
                    yx_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    yx_info.O_ZK_CODE = dr["ZK_CODE"].ToString();
                    yx_info.O_KT_LCODE = dr["KT_LCODE"].ToString();
                    if (dr["ZK_LATITUDE"].ToString() != "")
                        yx_info.O_ZK_LATITUDE = Convert.ToInt32(dr["ZK_LATITUDE"].ToString());
                    if (dr["ZK_LONGITUDE"].ToString() != "")
                        yx_info.O_ZK_LONGITUDE = Convert.ToInt32(dr["ZK_LONGITUDE"].ToString());

                    if (dr["ZK_X"].ToString() != "")
                        yx_info.O_ZK_X = float.Parse(dr["ZK_X"].ToString());
                    if (dr["ZK_Y"].ToString() != "")
                        yx_info.O_ZK_Y = float.Parse(dr["ZK_Y"].ToString());
                    if (dr["ZK_H"].ToString() != "")
                        yx_info.O_ZK_H = float.Parse(dr["ZK_H"].ToString());
                    if (dr["ZK_DEPTH"].ToString() != "")
                        yx_info.O_ZK_DEPTH = float.Parse(dr["ZK_DEPTH"].ToString());
                    if (dr["ZK_CORELEN"].ToString() != "")
                        yx_info.O_ZK_CORELEN = float.Parse(dr["ZK_CORELEN"].ToString());
                    if (dr["GC_CORELEN"].ToString() != "")
                        yx_info.O_GC_CORELEN = float.Parse(dr["GC_CORELEN"].ToString());
                    if (dr["ZKYX_COUNT"].ToString() != "")
                        yx_info.O_ZKYX_COUNT = int.Parse(dr["ZKYX_COUNT"].ToString());
                    if (dr["GCYX_COUNT"].ToString() != "")
                        yx_info.O_GCYX_COUNT = int.Parse(dr["GCYX_COUNT"].ToString());

                    yx_info.O_START_DATE = dr["START_DATE"].ToString();
                    yx_info.O_END_DATE = dr["START_DATE"].ToString();

                    yxLst.Add(yx_info);
                }

                return yxLst;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过fileID查询记录
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public YX_TBL SelectYXInfoByID(string zk_code)
        {
            YX_TBL yx_info = new YX_TBL();
            string strSql = "SELECT * FROM dbo.YX_TBL WHERE ZK_CODE ='";
            strSql += zk_code + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    yx_info.O_ID = int.Parse(dr["ID"].ToString());
                    yx_info.O_ZK_CODE = zk_code;
                    yx_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    yx_info.O_KT_LCODE = dr["KT_LCODE"].ToString();
                    if (dr["ZK_LATITUDE"].ToString() != "")
                        yx_info.O_ZK_LATITUDE = Convert.ToInt32(dr["ZK_LATITUDE"].ToString());
                    if (dr["ZK_LONGITUDE"].ToString() != "")
                     yx_info.O_ZK_LONGITUDE = Convert.ToInt32(dr["ZK_LONGITUDE"].ToString());

                    if (dr["ZK_X"].ToString() != "")
                        yx_info.O_ZK_X= float.Parse(dr["ZK_X"].ToString());
                    if (dr["ZK_Y"].ToString() != "")
                        yx_info.O_ZK_Y = float.Parse(dr["ZK_Y"].ToString());
                    if (dr["ZK_H"].ToString() != "")
                        yx_info.O_ZK_H = float.Parse(dr["ZK_H"].ToString());
                    if (dr["ZK_DEPTH"].ToString() != "")
                        yx_info.O_ZK_DEPTH = float.Parse(dr["ZK_DEPTH"].ToString());
                    if (dr["ZK_CORELEN"].ToString() != "")
                        yx_info.O_ZK_CORELEN = float.Parse(dr["ZK_CORELEN"].ToString());
                    if (dr["GC_CORELEN"].ToString() != "")
                        yx_info.O_GC_CORELEN = float.Parse(dr["GC_CORELEN"].ToString());
                    if (dr["ZKYX_COUNT"].ToString() != "")
                        yx_info.O_ZKYX_COUNT= int.Parse(dr["ZKYX_COUNT"].ToString());
                    if (dr["GCYX_COUNT"].ToString() != "")
                        yx_info.O_GCYX_COUNT = int.Parse(dr["GCYX_COUNT"].ToString());
                    
                    yx_info.O_START_DATE = dr["START_DATE"].ToString();
                    yx_info.O_END_DATE = dr["END_DATE"].ToString();
                }

                return yx_info;
            }
            catch (ArgumentNullException e)
            {
                throw e; 
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.YX_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 添加岩心记录
        /// </summary>
        /// <param name="yx_info"></param>
        /// <returns></returns>
        public int AddYXInfo(YX_TBL yx_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.YX_TBL(ZL_FILE_ID,ZK_CODE,KT_LCODE,ZK_LONGITUDE,ZK_LATITUDE,";
            strSql += "ZK_X,ZK_Y,ZK_H,ZK_DEPTH,ZK_CORELEN,GC_CORELEN,ZKYX_COUNT,GCYX_COUNT,";
            strSql += "START_DATE,END_DATE) VALUES(";
            strSql += "'" + yx_info.O_ZL_FILE_ID + "',";
            strSql += "'" + yx_info.O_ZK_CODE + "',";
            strSql += "'" + yx_info.O_KT_LCODE + "',";
            strSql += yx_info.O_ZK_LONGITUDE.ToString() + ",";
            strSql += yx_info.O_ZK_LATITUDE.ToString() + ",";
            strSql += yx_info.O_ZK_X.ToString() + ",";
            strSql += yx_info.O_ZK_Y.ToString() + ",";
            strSql += yx_info.O_ZK_H.ToString() + ",";
            strSql += yx_info.O_ZK_DEPTH.ToString() + ",";
            strSql += yx_info.O_ZK_CORELEN.ToString() + ",";
            strSql += yx_info.O_GC_CORELEN.ToString() + ",";
            strSql += yx_info.O_ZKYX_COUNT.ToString() + ",";
            strSql += yx_info.O_GCYX_COUNT.ToString() + ",";
            strSql += "'" + yx_info.O_START_DATE.ToString() + "',";
            strSql += "'" + yx_info.O_END_DATE.ToString() + "')";
          
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 通过file_ID 删除岩心记录
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int DeleteYXInfoById(int id)
        {
            string strSql = "DELETE FROM dbo.YX_TBL WHERE ID =";
            strSql += id.ToString();

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }




        /// <summary>
        /// 更新岩心记录
        /// </summary>
        /// <param name="yx_info"></param>
        /// <returns></returns>
        public int UpdateYXInfo(YX_TBL yx_info)
        {
            /*FILE_ID,ZK_CODE,KT_LCODE,ZK_LONGITUDE,ZK_LATITUDE,ZK_X,ZK_Y,ZK_H,
            ZK_DEPTH,ZK_CORELEN,GC_CORELEN,ZKYX_COUNT,GCYX_COUNT,START_DATE,END_DATE;*/

            string strSql = "UPDATE dbo.YX_TBL ";
            strSql += "SET ZK_CODE='" + yx_info.O_ZK_CODE + "',";
            strSql += " ZL_FILE_ID='" + yx_info.O_ZL_FILE_ID + "',";
            strSql += " KT_LCODE='" + yx_info.O_KT_LCODE + "',";
            strSql += " ZK_LONGITUDE=" + yx_info.O_ZK_LONGITUDE + ",";
            strSql += " ZK_LATITUDE=" + yx_info.O_ZK_LATITUDE + ",";
            strSql += " ZK_X=" + yx_info.O_ZK_X.ToString() + ",";
            strSql += " ZK_Y=" + yx_info.O_ZK_Y.ToString() + ",";
            strSql += " ZK_H=" + yx_info.O_ZK_H.ToString() + ",";
            strSql += " ZK_DEPTH=" + yx_info.O_ZK_DEPTH.ToString() + ",";
            strSql += " ZK_CORELEN=" + yx_info.O_ZK_CORELEN.ToString() + ",";
            strSql += " GC_CORELEN=" + yx_info.O_GC_CORELEN.ToString() + ",";
            strSql += " ZKYX_COUNT=" + yx_info.O_ZKYX_COUNT.ToString() + ",";
            strSql += " GCYX_COUNT=" + yx_info.O_GCYX_COUNT.ToString() + ",";
            strSql += " START_DATE='" + yx_info.O_START_DATE.ToString() + "',";
            strSql += " END_DATE='" + yx_info.O_END_DATE.ToString() + "'";
         
            strSql += "WHERE ID='" + yx_info.O_ID + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region =影像数据操作=
        public int AppendImage(int id, byte[] bImg, long len)
        {
            string SQL = "UPDATE  dbo.YX_TBL SET IMAGE = @IMAGE WHERE ID=" + id.ToString();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@IMAGE", SqlDbType.Image);
            commandParameters[0].Value = bImg;

            try
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, SQL, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }


            return 1;
        }

        public byte[] GetImage(int id)
        {
            byte[] photo = null;
            string sql = "SELECT IMAGE FROM dbo.YX_INFO_TBL WHERE ID =" + id.ToString();
            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.SQLConnString,CommandType.Text, sql, null);//采用SqlDataReader的方法来读取数据

            if (reader.Read()) 
                photo = reader[0] as byte[];//将第0列的数据写入byte数组 

            reader.Close();//关闭reader
            return photo;
        }

        #endregion
    }
}
